﻿<cfsilent>
 
    <cfset sql = "  SELECT a.sbj_name 
					FROM t_subject a 
					WHERE a.sbj_id = :SubjectId "/>
                    
    <cfset queryObj = new Query( datasource=application.dnsSlave ) />
    <cfset queryObj.addParam( name="SubjectId", value=event.getArg("SubID"), cfsqltype="cf_sql_varchar" )/>
    
    <cfset rs_subject = queryObj.execute( sql=sql ).getResult()/> 
    
	<cfset sql = "      SELECT a.teacher_in_charge,a.assistant,
                             a.cls_name,a.cls_size,
                             a.cls_scale,a.grade,
                             a.cls_found,t_campus.campus_name,
                             t_subject.sbj_name 
                        FROM t_class a 
                             INNER JOIN t_campus ON a.campus_id = t_campus.campus_id 
                             INNER JOIN t_subject ON t_subject.sbj_id = a.sbj_id 
                        WHERE a.sbj_id = :subjectId 
                        ORDER BY a.cls_name ASC,a.grade DESC "/>
                        
    <cfset queryObj = new Query( datasource=application.dnsSlave ) />
    <cfset queryObj.addParam( name="subjectId", value=event.getArg("SubID"), cfsqltype="cf_sql_varchar" )/>
    
    <cfset rs_class = queryObj.execute( sql=sql ).getResult()/>

	<cfset temFile = GetTempDirectory() & createUUID() & ".xls" />
	<cfset downFile = URLEncodedFormat(rs_subject.sbj_name & "行政班信息", "utf-8") />

	<cfset excel = getProperty("serviceFactory").getBean("spreadSheetObject") />

	<!--- 创建工作簿 --->
	<cfset spreadsheetObj = excel.SpreadSheetNew("行政班", false) />

	<!--- 添加表头 --->
	<cfset excel.SpreadsheetAddrow(spreadsheetObj, "班级,计划人数,实际人数,年级,建班年月,班主任,班长,辅导员,所在校区,所属专业") />

	<cfset row = 1 />
	 
    <cfloop query="rs_class" >
										
		<cfset row++ />
		
		<cfset TeacherInCharge = "" />
		<cfset Monitor = "" />
		<cfset Assistant = "" />
		
		<!--- 查询班主任 --->
		<cfif rs_class.teacher_in_charge neq ""> 
			<cfset sql= "   SELECT a.tch_id, a.tch_name  
                            FROM t_teacher a 
                            WHERE a.tch_id = :tchId "/>
                            
            <cfset queryObj = new Query( datasource=application.dnsSlave ) />
            <cfset queryObj.addParam( name="tchId", value=rs_class.teacher_in_charge, cfsqltype="cf_sql_varchar" )/>
            
            <cfset rs_teacher = queryObj.execute( sql=sql ).getResult()/>
            
			<cfif rs_teacher.recordCount>
				<cfset TeacherInCharge = rs_teacher.tch_name & "(" & rs_teacher.tch_id & ")" />
			</cfif>
		</cfif>
		
		<!--- 查询班长 --->
		
		<!--- 查询辅导员 --->
		<cfif rs_class.assistant neq ""> 
			<cfset sql= "   SELECT a.tch_id, a.tch_name  
                            FROM t_teacher a 
                            WHERE a.tch_id = :tchId "/>
                            
            <cfset queryObj = new Query( datasource=application.dnsSlave ) />
            <cfset queryObj.addParam( name="tchId", value=rs_class.assistant, cfsqltype="cf_sql_varchar" )/>
            
            <cfset rs_teachers = queryObj.execute( sql=sql ).getResult()/>
            
			<cfif rs_teachers.recordCount>
				<cfset Assistant = rs_teachers.tch_name & "(" & rs_teachers.tch_id & ")" />
			</cfif>
		</cfif>
		
		<!--- 构建班级信息 --->
		<cfset rowData = rs_class.cls_name
						& ","
						& rs_class.cls_size
						& ","
						& rs_class.cls_scale
						& ","
						& rs_class.grade
						& ","
						& rs_class.cls_found
						& ","
						& TeacherInCharge
						& ","
						& Monitor
						& ","
						& Assistant
						& ","
						& rs_class.campus_name
						& ","
						& rs_class.sbj_name
						/>
						
		<!--- 写入班级信息 --->	
		<cfset excel.SpreadsheetAddrow(spreadsheetObj, rowData) />
		
	</cfloop>
	
	<cfset excel.SpreadSheetWrite(spreadsheetObj, temFile, true) />

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>